﻿using System;
using System.Data;
using System.Data.SqlClient;
using DBModule;

namespace DataLogical.TableClass
{
    #region tblUser
    /// <summary>
    /// This object represents the properties and methods of a tblUser.
    /// </summary>

    public class tblUser
    {
        protected int _iD;
        protected string _userName = String.Empty;
        protected string _userCName = String.Empty;
        protected string _division = String.Empty;
        protected string _department = String.Empty;
        protected int _defaultApprover;
        protected int _status;
        protected string _employeeCode = String.Empty;

        public tblUser()
        {
        }

        public tblUser(int ID)
        {
            SqlModule db = CommonVar.s_SqlModule;
            string strSql = string.Format("SELECT ID,UserName,UserCName,Division,Department,DefaultApprover,Status,EmployeeCode FROM tblUser WHERE ID = {0}", ID);
            SqlDataReader reader = db.FastQueryBySql(strSql);
            if (reader.Read())
            {
                this.LoadFromReader(reader);
                reader.Close();
            }
            else
            {
                if (!reader.IsClosed) reader.Close();
                throw new ApplicationException("tblUser does not exist.");
            }
        }

        public tblUser(SqlDataReader reader)
        {
            this.LoadFromReader(reader);
        }

        protected void LoadFromReader(SqlDataReader reader)
        {
            if (reader != null && !reader.IsClosed)
            {
                _iD = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) _userName = reader.GetString(1);
                if (!reader.IsDBNull(2)) _userCName = reader.GetString(2);
                if (!reader.IsDBNull(3)) _division = reader.GetString(3);
                if (!reader.IsDBNull(4)) _department = reader.GetString(4);
                if (!reader.IsDBNull(5)) _defaultApprover = reader.GetInt32(5);
                if (!reader.IsDBNull(6)) _status = reader.GetInt32(6);
                if (!reader.IsDBNull(7)) _employeeCode = reader.GetString(7);
            }
        }

        public void Delete(int ID)
        {
            SqlModule db = CommonVar.s_SqlModule;
            string strSql = string.Format("Delete From tblUser Where ID={0}", ID);
            db.ExecSql(strSql);
        }

        public DataTable GetAll()
        {
            string strSql = "SELECT ID,UserName,UserCName,Division,Department,DefaultApprover,Status,EmployeeCode FROM tblUser";
            SqlModule db = CommonVar.s_SqlModule;
            return db.QueryBySql(strSql);
        }

        public int Insert(tblUser obj)
        {
            int rtn = 0;
            string strSql = "Insert Into tblUser(UserName,UserCName,Division,Department,DefaultApprover,Status,EmployeeCode)Values(@UserName,@UserCName,@Division,@Department,@DefaultApprover,@Status,@EmployeeCode);SELECT @@IDENTITY";
            SqlModule db = CommonVar.s_SqlModule;
            SqlModule.ParameterCollection Params = CreateParams(obj);
            DataTable dt = db.QueryBySql(strSql, Params);
            if (dt.Rows.Count == 1)
            {
                rtn = int.Parse(dt.Rows[0][0] + "");
            }
            return rtn;
        }

        public void Update(tblUser obj)
        {
            string strSql = string.Format("Update tblUser Set UserName=@UserName,UserCName=@UserCName,Division=@Division,Department=@Department,DefaultApprover=@DefaultApprover,Status=@Status,EmployeeCode=@EmployeeCode Where ID={0}", obj.ID);
            SqlModule db = CommonVar.s_SqlModule;
            SqlModule.ParameterCollection Params = CreateParams(obj);
            db.ExecSql(strSql, Params);
        }

        private SqlModule.ParameterCollection CreateParams(tblUser obj)
        {
            SqlParameter Param;
            SqlModule.ParameterCollection Result = new SqlModule.ParameterCollection();

            Param = new SqlParameter("@UserName", SqlDbType.VarChar);
            Result.Add(Param);
            if (obj.UserName != null)
            {
                Param.Value = obj.UserName;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            Param = new SqlParameter("@UserCName", SqlDbType.VarChar);
            Result.Add(Param);
            if (obj.UserCName != null)
            {
                Param.Value = obj.UserCName;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            Param = new SqlParameter("@Division", SqlDbType.VarChar);
            Result.Add(Param);
            if (obj.Division != null)
            {
                Param.Value = obj.Division;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            Param = new SqlParameter("@Department", SqlDbType.VarChar);
            Result.Add(Param);
            if (obj.Department != null)
            {
                Param.Value = obj.Department;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            Param = new SqlParameter("@DefaultApprover", SqlDbType.Int);
            Result.Add(Param);
            if (obj.DefaultApprover != null)
            {
                Param.Value = obj.DefaultApprover;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            Param = new SqlParameter("@Status", SqlDbType.Int);
            Result.Add(Param);
            if (obj.Status != null)
            {
                Param.Value = obj.Status;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            Param = new SqlParameter("@EmployeeCode", SqlDbType.VarChar);
            Result.Add(Param);
            if (obj.EmployeeCode != null)
            {
                Param.Value = obj.EmployeeCode;
            }
            else
            {
                Param.Value = DBNull.Value;
            }
            return Result;
        }

        public static bool Exist(int ID)
        {
            SqlModule db = CommonVar.s_SqlModule;
            string strSql = string.Format("SELECT COUNT(*) From tblUser Where ID={0}", ID);
            return (db.ExecSql_Cnt(strSql) == 1);
        }

        #region Public Properties
        public int ID
        {
            get { return _iD; }
        }

        public string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        public string UserCName
        {
            get { return _userCName; }
            set { _userCName = value; }
        }

        public string Division
        {
            get { return _division; }
            set { _division = value; }
        }

        public string Department
        {
            get { return _department; }
            set { _department = value; }
        }

        public int DefaultApprover
        {
            get { return _defaultApprover; }
            set { _defaultApprover = value; }
        }

        public int Status
        {
            get { return _status; }
            set { _status = value; }
        }

        public string EmployeeCode
        {
            get { return _employeeCode; }
            set { _employeeCode = value; }
        }
        #endregion
    }
    #endregion
}
